package com.me.dao.impl;

import com.me.dao.IPetDao;
import com.me.domain.Pet;
import com.me.domain.User;
import com.me.util.DBHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class PetDao implements IPetDao {

    @Override
    public List<Pet> getPetsByOwnerId(int ownerId) {
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "select * from pet p where p.ownerID=?";
            return runner.query(sql, new BeanListHandler<Pet>(Pet.class),ownerId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public void save(Pet pet){
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "insert into pet value(null,?,?,?,?)";
            runner.update(sql,pet.getName(),pet.getBirthdate(),pet.getPhoto(),pet.getOwnerId());
	        String sql2 = "select * from pet order by id desc";
	        pet.setId(runner.query(sql2, new BeanHandler<Pet>(Pet.class)).getId());
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    public void delete(int petId){
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "delete from pet where id=?";
            runner.update(sql,petId);
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

	@Override
	public List<Pet> getAll() {
		try {
			QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
			String sql = "select * from pet";
			return runner.query(sql,new BeanListHandler<Pet>(Pet.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Pet getById(String id) {
		try {
			QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
			String sql = "select * from pet where id=?";
			return runner.query(sql,new BeanHandler<Pet>(Pet.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<Pet> search(String username, String petname) {
		try {
			QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
			String sql = "select p.* from pet p,user u where p.ownerID=u.id and u.name like ? and p.name like ?";
			List<Pet> pets = runner.query(sql, new BeanListHandler<Pet>(Pet.class), "%" + username + "%", "%" + petname + "%");
			for (Pet p : pets) {
				String sql2 = "select * from user u where u.id=?";
				p.setOwner(runner.query(sql2,new BeanHandler<User>(User.class),p.getOwnerId()));
			}
			return pets;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public void update(Pet pet) {
		try {
			QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
			String sql = "UPDATE `pet`.`pet` SET `name` = ?, `birthdate` = ?, `photo` = ?, `ownerID` = ? WHERE (`id` = ?);";
			runner.update(sql, pet.getName(), pet.getBirthdate(), pet.getPhoto(), pet.getOwnerId(), pet.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


}
